﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataModel;

namespace SchoolDataLayer
{
    public class DLRoutine
    {
        public int AddStudent(Subject _subject)
        {
            using (SqlConnection conn = DBConn.GetConnectionString())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "ProcAddSubject";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@subname", _subject.SubjectName);
                    cmd.Parameters.AddWithValue("@subcode", _subject.SubjectCode);
                    cmd.Parameters.AddWithValue("@classid", _subject.ClassGrade.ClassId);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public List<Subject> GetAllSubject()
        {
            List<Subject> _subject = new List<Subject>();
            Grade _gr = new Grade();
            //_subject.
            SqlConnection conn=DBConn.GetConnectionString();
            string proc="ProcGetAllSubject";
            SqlCommand cmd=new SqlCommand(proc,conn);
            cmd.CommandType=CommandType.StoredProcedure;
            SqlDataReader reader=cmd.ExecuteReader();
            if(reader!=null)
            {
            while(reader.Read())
            {
               // _gr.ClassName = (string)reader["Grade"];
                _subject.Add(
                new Subject
                {
                    SubjectId = (int)reader["SubjectId"],
                    SubjectName = (String)reader["SubjectName"],
                    SubjectCode = (String)reader["SubjectCode"],
                    className=(string)reader["Grade"]
                                                         }
                );
               }
            }
             cmd.Dispose();
            reader.Dispose();
            return _subject;
        }

        public List<Grade> GetGrade()
        {
            List<Grade> _grade=new List<Grade>();
            SqlConnection conn = DBConn.GetConnectionString();
            string proc = "ProcGetGrade";
            SqlCommand cmd = new SqlCommand(proc, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    _grade.Add(
                        new Grade()
                        {
                            ClassId = (int)reader["ClassId"],
                            ClassName = (string)reader["Grade"]
                        }
                        );
                }
            }
            cmd.Dispose();
            reader.Dispose();
            return _grade;
        }

        public List<MStudent> GetSection(int grade)
        {
            List<MStudent> stu = new List<MStudent>();
            SqlConnection conn = DBConn.GetConnectionString();
            string proc = "ProcGetSectionByClass";
            SqlCommand cmd = new SqlCommand(proc, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@grade",grade);
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    stu.Add(
                        new MStudent()
                        {

                            section = (string)reader["Section"]
                        }
                        );
                }
            }
            cmd.Dispose();
            reader.Dispose();
            return stu;
        }

        public Subject GetSubjectById(int id) 
        {
             Subject subject = new Subject();
             Grade gr = new Grade();
             subject.ClassGrade = gr;
            using (SqlConnection conn = DBConn.GetConnectionString())
            {
                using (SqlCommand cmd =conn.CreateCommand())
                {
                    cmd.CommandText = "ProcGetSubjectById";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@subjectId", id);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            subject.SubjectName = (string)reader["SubjectName"];
                            subject.SubjectCode = (string)reader["SubjectCode"];
                            subject.ClassGrade.ClassName =  (string)reader["Grade"] ;
                         }
                              
                        }
                    cmd.Dispose();
                    reader.Dispose();
                }
             }
             return subject;

        }
        //GetSubject By Class
        public List<Subject> GetSubjectByClassId(int classId)
        {
            List<Subject> subject = new List<Subject>();
            using (SqlConnection conn = DBConn.GetConnectionString())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "ProcGetSubjectClass";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@classid", classId);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            subject.Add(
                                new Subject{
                            SubjectName = (string)reader["SubjectName"],
                            SubjectId=(int)reader["SubjectId"]
                                }
                            );
                        }

                    }
                    cmd.Dispose();
                    reader.Dispose();
                }
            }
            return subject;

        }

        public int UpdateSubjectById(Subject _subject)
        {
            using (SqlConnection conn = DBConn.GetConnectionString())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "EditSubject";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@subjectid", _subject.SubjectId);
                    cmd.Parameters.AddWithValue("@subjectname", _subject.SubjectName);
                    cmd.Parameters.AddWithValue("@subjestcode", _subject.SubjectCode);
                    cmd.Parameters.AddWithValue("@ClassName", _subject.ClassGrade.ClassId);
                    return cmd.ExecuteNonQuery();

                
                }
            }
        }

        //delete subject
        public int DeleteSubjectbySubjectId(int subId)
        {
            using (SqlConnection conn = DBConn.GetConnectionString())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "ProcDeleteSubject";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@subjectid", subId);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int SaveRoutine(Rountine _routine)
        {
            using (SqlConnection conn = DBConn.GetConnectionString())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = "ProcAddClassRountine";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@period",_routine.Period);
                    cmd.Parameters.AddWithValue("@subjectId", _routine.SubjectId);
                    cmd.Parameters.AddWithValue("@classId", _routine.ClassId);
                    cmd.Parameters.AddWithValue("@startTime", _routine.StartTime);
                    cmd.Parameters.AddWithValue("@endTime", _routine.EndTime);
                    cmd.Parameters.AddWithValue("@teacherId", _routine.TeacherId);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        }
}
